#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "
-- 建库
create database if not exists ol_edu_dwb;

drop table if exists ol_edu_dwb.dwb_intent_detail;
create table if not exists ol_edu_dwb.dwb_intent_detail
(
    --意向表
    intent_id int comment '意向id',
    create_date_time string,
    update_date_time string,
    intent_deleted int comment '是否删除',
    customer_id int comment '学员id',
    origin_type  string comment '线上线下',
    year string,
    month string,
    day string,
    --客户表
    area string comment '地区',
    --线索表
    clue_id int comment '线索id',
    customer_relationship_id int comment '意向id',
    clue_state string comment '新老学员',
    itcast_school_id int comment '校区id',
    itcast_school string comment '校区名称',
    itcast_subject_id int comment '学科id',
    itcast_subject string comment '学科名称',
    origin_channel string comment '来源渠道',
    --员工表
    employee_id int comment '员工id',
    --部门表
    department_id int comment '部门id',
    department_name string comment '部门名称',
    parent_id int comment '父id'
)comment '意向主题明细表'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='snappy');


drop table if exists ol_edu_dwb.dwb_clue_detail;
create table if not exists ol_edu_dwb.dwb_clue_detail
(
    create_date_time string,
    origin_type  string comment '线上线下',
    clue_state string comment '新老学员',
    clue_id int comment '线索id',
    appeal_status string comment '稽核状态',
    day string,
    hour string
)comment '线索主题明细表'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='snappy');
"